package com.meeting.meetingroom_api.repository;

import com.meeting.meetingroom_api.entity.Booking;
import com.meeting.meetingroom_api.entity.MeetingRoom;
import com.meeting.meetingroom_api.entity.User;
import com.meeting.meetingroom_api.enums.BookingStatus;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Optional;

@Repository
public interface BookingRepository extends JpaRepository<Booking, Integer>, JpaSpecificationExecutor<Booking> {

    List<Booking> findByCustomer(User customer);

    List<Booking> findByRoom(MeetingRoom room);

    List<Booking> findByStatus(BookingStatus status);

    // Find overlapping bookings for a specific room (只返回未过期的预订)
    @Query("SELECT b FROM Booking b WHERE b.room = :room " +
            "AND b.status IN :statuses " +
            "AND b.endDatetime > CURRENT_TIMESTAMP " +  // 添加时间过滤：只考虑未结束的预订
            "AND ((b.startDatetime < :endDateTime AND b.endDatetime > :startDateTime))")
    List<Booking> findOverlappingBookings(@Param("room") MeetingRoom room,
                                          @Param("startDateTime") LocalDateTime startDateTime,
                                          @Param("endDateTime") LocalDateTime endDateTime,
                                          @Param("statuses") List<BookingStatus> statuses);

    // 查询与指定时间段有重叠的预订（包括完全或部分重叠）- 只返回未过期的预订
    @Query("SELECT b FROM Booking b " +
            "WHERE b.room.roomId IN :roomIds " +
            "AND b.status IN :validStatuses " +
            "AND b.endDatetime > CURRENT_TIMESTAMP " +  // 添加时间过滤：只考虑未结束的预订
            "AND (b.startDatetime < :endTime AND b.endDatetime > :startTime) " + // 关键条件：预订与时间段重叠
            "ORDER BY b.startDatetime ASC")
    List<Booking> findPeriodBookings(
            @Param("roomIds") List<Integer> roomIds,
            @Param("startTime") LocalDateTime startTime,
            @Param("endTime") LocalDateTime endTime,
            @Param("validStatuses") List<BookingStatus> validStatuses
    );
    // 查询与指定时间段有重叠的预订（包括完全或部分重叠）- 只返回未过期的预订
    @Query("SELECT b FROM Booking b " +
            "WHERE b.room.roomId = :roomId " +
            "AND b.status IN :validStatuses " +
            "AND b.endDatetime > CURRENT_TIMESTAMP " +  // 添加时间过滤：只考虑未结束的预订
            "AND (b.startDatetime < :endTime AND b.endDatetime > :startTime) " + // 关键条件：预订与时间段重叠
            "ORDER BY b.startDatetime ASC")
    List<Booking> findOnePeriodBookings(
            @Param("roomId") Integer roomId,
            @Param("startTime") LocalDateTime startTime,
            @Param("endTime") LocalDateTime endTime,
            @Param("validStatuses") List<BookingStatus> validStatuses
    );
    // Find bookings that need payment and their deadline has passed
    List<Booking> findByStatusAndPaymentDeadlineBefore(BookingStatus status, LocalDateTime now);

    //按客户和状态查询
    Page<Booking> findByCustomerAndStatus(User customer, BookingStatus status, Pageable pageable);

    Page<Booking> findByCustomerAndStatusIn(User customer, List<BookingStatus> statuses, Pageable pageable);

    // Find bookings for a customer within a date range
    List<Booking> findByCustomerAndStartDatetimeBetween(User customer, LocalDateTime startDate, LocalDateTime endDate);

    Optional<Booking> findByBookingIdAndCustomer(Integer bookingId, User customer);

    // Check if a room has active or upcoming bookings
    boolean existsByRoomAndStatusIn(MeetingRoom room, List<BookingStatus> statuses);

    // Check if a room has active or upcoming bookings (只检查未过期的预订)
    @Query("SELECT COUNT(b) > 0 FROM Booking b WHERE b.room = :room " +
           "AND b.status IN :statuses " +
           "AND b.endDatetime > CURRENT_TIMESTAMP")
    boolean existsByRoomAndStatusInAndNotExpired(@Param("room") MeetingRoom room, 
                                                @Param("statuses") List<BookingStatus> statuses);

    // 统计指定会议室下状态为active且ID不等于当前订单的数量（只统计未过期的预订）
    @Query("SELECT COUNT(b) FROM Booking b WHERE b.room.roomId = :roomId " +
           "AND b.status IN :statuses " +
           "AND b.bookingId != :bookingId " +
           "AND b.endDatetime > CURRENT_TIMESTAMP")
    long countByRoomRoomIdAndStatusInAndBookingIdNotAndNotExpired(
            @Param("roomId") Integer roomId,
            @Param("statuses") List<BookingStatus> statuses,
            @Param("bookingId") Integer bookingId
    );

    // 查询指定会议室的活跃订单（排除当前订单，只返回未过期的预订）
    @Query("SELECT b FROM Booking b WHERE b.room.roomId = :roomId " +
           "AND b.status IN :statuses " +
           "AND b.bookingId != :excludedId " +
           "AND b.endDatetime > CURRENT_TIMESTAMP")
    List<Booking> findByRoomRoomIdAndStatusInAndBookingIdNotAndNotExpired(
            @Param("roomId") Integer roomId,
            @Param("statuses") List<BookingStatus> statuses,
            @Param("excludedId") Integer excludedId
    );

    Page<Booking> findByCustomer(User customer, Pageable pageable);

    Page<Booking> findByRoom(MeetingRoom room, Pageable pageable);

    // 查询指定状态和时间范围内的预订
    Page<Booking> findByStatusAndStartDatetimeBetween(
            BookingStatus status, LocalDateTime startDateTime, LocalDateTime endDateTime, Pageable pageable);

    // 查询指定状态的预订
    Page<Booking> findByStatus(BookingStatus status, Pageable pageable);

    // 查询指定时间范围内的预订
    Page<Booking> findByStartDatetimeBetween(LocalDateTime startDateTime, LocalDateTime endDateTime, Pageable pageable);

    // 根据状态和截止时间范围查询预订
    List<Booking> findByStatusAndPaymentDeadlineBetween(BookingStatus status, LocalDateTime startDate, LocalDateTime endDate);

    // 查找过期的预订（结束时间已过但状态仍为confirmed或in_progress）
    @Query("SELECT b FROM Booking b WHERE " +
           "(b.status = :confirmedStatus OR b.status = :inProgressStatus) " +
           "AND b.endDatetime < :currentTime")
    List<Booking> findExpiredActiveBookings(@Param("confirmedStatus") BookingStatus confirmedStatus,
                                           @Param("inProgressStatus") BookingStatus inProgressStatus,
                                           @Param("currentTime") LocalDateTime currentTime);
}